-- ================================================================
--
-- 	Problem 1	
--	Author 	: Isaline Laurent & Paul Laturaze
--	Date 	: 09/11/2012
--
-- ================================================================

-- ================================================================
--
--	check_advisor function
--
-- ================================================================
CREATE OR REPLACE FUNCTION check_advisor(IN student_id VARCHAR(5)) 
RETURNS INTEGER AS $$
-- DECLARING ALIASES FOR AN EASIER LECTURE
DECLARE 
	student_id ALIAS FOR $1; 	-- The function argument
	to_compare RECORD;
	returnValue INTEGER;		-- The value to return
BEGIN
	-- Getting all information to check into a record
	SELECT i_ID, s_ID, instructor.dept_name AS i_dept_name, student.dept_name AS s_dept_name FROM advisor 
		INNER JOIN student ON s_ID = student.ID 
		INNER JOIN instructor ON i_ID = instructor.ID 
		WHERE s_ID = student_id
	INTO to_compare;

	-- Checking if at least one of condition of adv is satisfied
	IF to_compare.i_ID IS NULL OR 
		to_compare.i_dept_name IS NULL OR
		to_compare.s_dept_name IS NULL OR
		to_compare.i_dept_name = to_compare.s_dept_name
	THEN
		returnValue := 1;
	ELSE
		returnValue := 0;
	END IF;
	RETURN  returnValue;
END;
$$ LANGUAGE plpgsql;


-- ================================================================
--
--	Trigger	
--
-- ================================================================

-- ================================================================
--
--	preserve_adv trigger on insert or update on advisor
--
-- ================================================================
CREATE OR REPLACE FUNCTION preserve_adv() 
RETURNS TRIGGER AS $preserve_adv$
DECLARE 
	check_adv INTEGER;		-- return value of check_advisor() function
	adv_dept_name VARCHAR(20);	-- Advisor department name
	adv_id VARCHAR(5);		-- Advisor id
	student_dept_name VARCHAR(20);	-- Student department name
	
BEGIN
	-- If we try to update the student id with a null value, it will violate a constraint on the primary key
	IF NEW.s_ID IS NULL THEN
		RAISE EXCEPTION 'ERROR : Student ID cant be NULL';
	END IF;
	
	-- Checking adv constraint
	SELECT check_advisor(NEW.s_ID) INTO check_adv;

	-- If check_advisor()  returns false
	IF check_adv = 0 THEN
		-- Getting the advisor for student of id student ID
		SELECT i_ID FROM advisor WHERE s_ID = NEW.s_ID INTO adv_id;
		-- Getting the department name for instructor with the previously gotten id
		SELECT dept_name FROM instructor WHERE ID = adv_id INTO adv_dept_name;
		-- Getting the department name for the student with id student_id
		SELECT dept_name FROM student WHERE ID = NEW.s_ID INTO student_dept_name;	
		IF adv_dept_name <> student_dept_name THEN
			RAISE EXCEPTION 'Student department name must match Advisor department name';
		END IF;
	ELSE 
		RAISE NOTICE 'Request executed normally';
		RETURN NEW;
	END IF;
END;
$preserve_adv$ LANGUAGE plpgsql;

DROP TRIGGER preserve_adv ON advisor;
CREATE TRIGGER preserve_adv AFTER INSERT OR UPDATE ON advisor
FOR EACH ROW EXECUTE PROCEDURE preserve_adv();

-- ================================================================
--
--	preserve_adv_student trigger on update on student
--
-- ================================================================
CREATE OR REPLACE FUNCTION preserve_adv_student()
RETURNS TRIGGER AS $preserve_adv_student$
DECLARE
	instructor RECORD;
	error INTEGER;
	check_adv INTEGER;
BEGIN
		-- Checking if the current student's advisor is in the same department
		SELECT check_advisor(NEW.id) INTO check_adv;
		IF check_adv = 0 THEN
			SELECT i_ID FROM advisor WHERE s_ID = NEW.ID INTO instructor;
			RAISE EXCEPTION 'Constraint violated for student % and instructor %', NEW.ID, instructor.i_ID;
		ELSE
			RAISE NOTICE 'Request executed normally';
			RETURN NEW;
		END IF;
END;
$preserve_adv_student$ LANGUAGE plpgsql;

-- ================================================================
--
--	preserve_adv_instructor trigger on update on instructor
--
-- ================================================================
CREATE OR REPLACE FUNCTION preserve_adv_instructor()
RETURNS TRIGGER AS $preserve_adv_instructor$
DECLARE
	student RECORD;
	error INTEGER;
	check_adv INTEGER;
BEGIN
		-- Checking if all students for the advisor are in the same department 
		FOR student IN SELECT s_ID FROM advisor WHERE i_ID = NEW.ID LOOP
			SELECT check_advisor(student.s_ID) INTO check_adv;
			IF check_adv <> 1 THEN
				RAISE EXCEPTION 'Constraint violated for student %', student.s_ID;
			END IF;
		END LOOP;
		-- If no exception has been raised	
		RAISE NOTICE 'Request executed normally';
		RETURN NEW;
END;
$preserve_adv_instructor$ LANGUAGE plpgsql;

DROP TRIGGER preserve_adv_instructor ON instructor;
DROP TRIGGER preserve_adv_student ON student;

CREATE TRIGGER preserve_adv_instructor AFTER UPDATE ON instructor
FOR EACH ROW EXECUTE PROCEDURE preserve_adv_instructor();

CREATE TRIGGER preserve_adv_student AFTER UPDATE ON student
FOR EACH ROW EXECUTE PROCEDURE preserve_adv_student();
